using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
using System.Collections.Generic;
using Pms.Utils;
using Biblioteca.Utils;
using Biblioteca.DataBase;
/*
 * 
 * Autor      : Jose Jornando de Carvalho Junior    
 * Data       : 10/04/2010 07:06:42
 * Descricao  : 
 * Alteracoes : 
 * 
 */

namespace SERIX
{
   public class CDescricoesOpcoes
   {
       private string IDT_DESC ;
       private string TPO_VINC ;
       private string DESCRICAO ;
       private string IDT_TPO_MED;
       private string IDT_TPO_MED_;
       private string IDT_TPO_MED__;

	   /// <sumary>
       /// Construtor da classe descricoes_opcoes
       /// <sumary>
       public CDescricoesOpcoes()
       {
   
       }

	   /// <sumary>
       /// Construtor da classe Descricoes_opcoes
       /// <sumary>
       public CDescricoesOpcoes(string IDT_DESC, string TPO_VINC, string DESCRICAO, string IDT_TPO_MED, string IDT_TPO_MED_, string IDT_TPO_MED__)
       {
          this.IDT_DESC = IDT_DESC;
          this.TPO_VINC = TPO_VINC;
          this.DESCRICAO = DESCRICAO;
          this.IDT_TPO_MED = IDT_TPO_MED;
          this.IDT_TPO_MED_ = IDT_TPO_MED_;
          this.IDT_TPO_MED__ = IDT_TPO_MED__;
       }
	   
	   #region Propriedades
     	/// <sumary>
        /// Identificador da descri??o da opcao da questao
        /// <sumary>
        public string Idt_desc 
		{
		   get
		   {
		       return this.IDT_DESC;
		   }
		   set
		   {
		       this.IDT_DESC = value;
		   }
		}
     	/// <sumary>
        /// Tipo de descricao da opcao da questao: E - Especie Animal, C - Cultura, B - Bem de Consumo
        /// <sumary>
        public string Tpo_vinc 
		{
		   get
		   {
		       return this.TPO_VINC;
		   }
		   set
		   {
		       this.TPO_VINC = value;
		   }
		}
     	/// <sumary>
        /// Descricao da opcao da questao
        /// <sumary>
        public string Descricao 
		{
		   get
		   {
		       return this.DESCRICAO;
		   }
		   set
		   {
		       this.DESCRICAO = value;
		   }
		}
        public string idt_tpo_med
        {
            get { return this.IDT_TPO_MED; }
            set { this.IDT_TPO_MED = value; }
        }
        public string idt_tpo_med_
        {
            get { return this.IDT_TPO_MED_; }
            set { this.IDT_TPO_MED_ = value; }
        }
        public string idt_tpo_med__
        {
            get { return this.IDT_TPO_MED__; }
            set { this.IDT_TPO_MED__ = value; }
        }
	    #endregion Propriedades
	
	    /// <summary>
        /// Inserindo um novo Descricoes_opcoes.
        /// </summary>
        public void Inserir(string Login)
        {
		    OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"insert into Serix.SRX_DESCRICOES_OPCOES
			                  ( IDT_DESC, TPO_VINC, DESCRICAO, IDT_TPO_MED, IDT_TPO_MED_,IDT_TPO_MED__ )
						   values
						      ( :IDT_DESC, :TPO_VINC, :DESCRICAO, :IDT_TPO_MED, :IDT_TPO_MED_,:IDT_TPO_MED__ )";
     		
			try
			{	
				command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
				
				command.Parameters.Add(new OracleParameter("IDT_DESC",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("TPO_VINC",OracleType.Char));
                command.Parameters.Add(new OracleParameter("DESCRICAO", OracleType.VarChar)); 
				command.Parameters.Add(new OracleParameter("IDT_TPO_MED",OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_TPO_MED_", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_TPO_MED__", OracleType.Number)); 
				
                command.Parameters["IDT_DESC"].Value = this.IDT_DESC;
                command.Parameters["TPO_VINC"].Value = this.TPO_VINC;
                command.Parameters["DESCRICAO"].Value = this.DESCRICAO;
                command.Parameters["IDT_TPO_MED"].Value = this.IDT_TPO_MED;
                if (this.IDT_TPO_MED_ == "")
                {
                    command.Parameters["IDT_TPO_MED_"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["IDT_TPO_MED_"].Value = this.IDT_TPO_MED_;
                }
                if (this.IDT_TPO_MED__ == "")
                {
                    command.Parameters["IDT_TPO_MED__"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["IDT_TPO_MED__"].Value = this.IDT_TPO_MED__;
                }
				command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
		}
		
	    /// <summary>
        /// Alterando um Descricoes_opcoes.
        /// </summary>
        public void Alterar(string Login)
        {
		    OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"update Serix.SRX_DESCRICOES_OPCOES set 
			                   TPO_VINC = :TPO_VINC, DESCRICAO = :DESCRICAO, IDT_TPO_MED = :IDT_TPO_MED, IDT_TPO_MED_ = :IDT_TPO_MED_,IDT_TPO_MED__ = :IDT_TPO_MED__
						   where
						       IDT_DESC = :IDT_DESC ";
     		
			try
			{	
				command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                command.Parameters.Add(new OracleParameter("IDT_DESC", OracleType.Number));
                command.Parameters.Add(new OracleParameter("TPO_VINC", OracleType.Char));
                command.Parameters.Add(new OracleParameter("DESCRICAO", OracleType.VarChar));
                command.Parameters.Add(new OracleParameter("IDT_TPO_MED", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_TPO_MED_", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_TPO_MED__", OracleType.Number));

                command.Parameters["IDT_DESC"].Value = this.IDT_DESC;
                command.Parameters["TPO_VINC"].Value = this.TPO_VINC;
                command.Parameters["DESCRICAO"].Value = this.DESCRICAO;
                command.Parameters["IDT_TPO_MED"].Value = this.IDT_TPO_MED;
                if (this.IDT_TPO_MED_ == "")
                {
                    command.Parameters["IDT_TPO_MED_"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["IDT_TPO_MED_"].Value = this.IDT_TPO_MED_;
                }
                if (this.IDT_TPO_MED__ == "")
                {
                    command.Parameters["IDT_TPO_MED__"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["IDT_TPO_MED__"].Value = this.IDT_TPO_MED__;
                }
				command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
		}
		
	     /// <summary>
        /// Exclui um  Descricoes_opcoes.
        /// </summary>
        public void Excluir(string Login)
        {
            Tabela tab = new Tabela("SRX_DESCRICOES_OPCOES", BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login,BiblioPMS.Demo));
            tab.adicionarFiltro(new Coluna("IDT_DESC", DbType.Int32, this.IDT_DESC));
            tab.delete();
        }
		
		static public DataTable ObterTodos(string login)
        {
            
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_DESCRICOES_OPCOES order by DESCRICAO ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection)); 
                return tbl; 
                
            }
            catch (Exception e)
            {
 
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }

        static public DataTable PesquisaporTpoVinc(string tpo_vinc, string login)
        {
            OracleCommand command = new OracleCommand();

            string SQL = @"SELECT *
                            FROM  Serix.SRX_DESCRICOES_OPCOES 
                            WHERE tpo_vinc = :tpo_vinc";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.AddWithValue("tpo_vinc", tpo_vinc);
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;

            }
            catch (Exception e)
            {

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
		
		static public CDescricoesOpcoes PesquisaporCodigo(string idt_desc, string login)
        {
            CDescricoesOpcoes obj;
            string SQL = @"SELECT *
                            FROM  Serix.SRX_DESCRICOES_OPCOES 
                            WHERE IDT_DESC = :IDT_DESC";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
				qy.AdicionarParametro(":IDT_DESC", DbType.String, idt_desc);
               
				qy.Executar();
                obj = new CDescricoesOpcoes(qy.ObterCampo("IDT_DESC"), qy.ObterCampo("TPO_VINC"), qy.ObterCampo("DESCRICAO"), qy.ObterCampo("IDT_TPO_MED"), qy.ObterCampo("IDT_TPO_MED_"), qy.ObterCampo("IDT_TPO_MED__"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return new CDescricoesOpcoes();
            }
			finally
            {
                qy.FecharConexao();
            }
            return obj;
        }

	
		static public int ObterNovoIdt(string login)
        {
            string SQL = @"SELECT PKG_BIBSERIX.OBTER_IDT_DESC() IDT From dual ";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
				qy.Executar();
				return Convert.ToInt32(qy.ObterCampo("IDT"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return-1;
            }
			finally
            {
                qy.FecharConexao();
            }
        }


        static public DataTable PreencherDropDown(string vinculo)
        {
            string sql = "SELECT DESCRICAO, IDT_DESC FROM SRX_DESCRICOES_OPCOES WHERE (TPO_VINC = :TPO_VINC) ORDER BY DESCRICAO";
            OracleConnection conexao = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            Query qy = new Query(conexao, sql);
            qy.AdicionarParametro("TPO_VINC", DbType.String, vinculo);
            DataTable datatable = qy.ObterDataTable();

            return datatable;
        }

        static public DataTable PreencherGrid(string vinculo, string idt)
        {
            string sql = "SELECT IDT_DESC, BENS_BENEF, IDT_BENS_BENEF, QUANT_BENEF, ANO_BENEF FROM SRX_BENS_BENEFICIARIO WHERE Idt_Benef = :Idt_Benef AND TPO_VINC = :TPO_VINC AND DATA_FIM IS NULL";
            OracleConnection conexao = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            Query qy = new Query(conexao, sql);
            qy.AdicionarParametro("Idt_Benef", DbType.String, idt);
            qy.AdicionarParametro("TPO_VINC",DbType.String, vinculo);
            DataTable datatable = qy.ObterDataTable();

            return datatable;
        }
    }
}	

